const executeSql = require('../utils/query.js')

exports.getArticleCount = async () => {
    let sql = `select count(article_id) as count from article`
    //  根据查询条件，有传条件，动态的拼接sql语句
    let result = await executeSql(sql)
    return result[0].count
}

// 函数的参数是对象，可以通过{}解构出其属性
exports.getArticlePage = async ({
                                    page, pageSize, title, status, category_id, tag_ids
                                }) => {
    // 计算分页偏移量
    const offset = (parseInt(page) - 1) * parseInt(pageSize);

    // 构建基础查询
    let baseQuery = `
      SELECT t1.*, t2.category_name 
      FROM article t1 
      LEFT JOIN category t2 ON t1.category_id = t2.category_id
  `;

    // 构建条件数组
    const conditions = [];

    // 添加标题条件（模糊匹配）
    if (title) {
        conditions.push(`t1.title LIKE '%${title}%'`);
    }

    // 添加状态条件
    if (status !== undefined) {
        conditions.push(`t1.status = ${parseInt(status)}`);
    }

    // 添加分类条件
    if (category_id !== undefined) {
        conditions.push(`t1.category_id = ${parseInt(category_id)}`);
    }

    // 添加标签条件（最多3个标签）
    if (tag_ids && tag_ids.length > 0) {
        // 确保最多只处理3个标签
        const validTagIds = tag_ids.slice(0, 3)
            .filter(tagId => !isNaN(tagId))
            .map(tagId => parseInt(tagId));

        validTagIds.forEach(tagId => {
            conditions.push(`FIND_IN_SET(${tagId}, t1.tag_ids) > 0`);
        });
    }

    // 组合WHERE子句
    let whereClause = '';
    if (conditions.length > 0) {
        whereClause = ' WHERE ' + conditions.join(' AND ');
    }

    // 构建完整查询SQL
    const sql = baseQuery + whereClause + ` LIMIT ${offset}, ${parseInt(pageSize)}`;

    // 构建统计总数SQL - 使用相同的WHERE条件
    const countSql = `SELECT COUNT(*) AS total FROM article t1 ${whereClause}`;

    try {
        // 使用原始的excuteSql函数
        const list = await executeSql(sql);
        const countResult = await executeSql(countSql);

        // 获取总数 - 修正字段名
        const total = countResult[0].total;
        return {
            list,
            total
        };
    } catch (error) {
        // 添加详细的错误日志
        console.error('SQL执行错误:');
        console.error('查询SQL:', sql);
        console.error('统计SQL:', countSql);

        throw new Error(`数据库查询失败: ${error.message}`);
    }
};

exports.insertArticle = async (data) => {
    console.log(data)
    let {
        title,
        content,
        publish_date,
        intro,
        status,
        cover,
        tag_ids,
        category_id,
        add_date,
        update_date,
        admin_id
    } = data

    let sql = `insert into article
        (title,
        content,
        publish_date,
        intro,
        status,
        cover,
        tag_ids,
        category_id,
        add_date,
        update_date,
        admin_id) 
            values
        ('${title}', '${content}', ${publish_date}, '${intro}', ${status}, '${cover}','${tag_ids}',${category_id}, ${add_date}, ${update_date}, ${admin_id})`

    let result = await executeSql(sql)
    return result
}

exports.updateArticleStatus = async (article_id, newStatus) => {
    let sql = `update article set status = ${newStatus} where article_id = ${article_id}`
    return await executeSql(sql)
}

exports.deleteArticleById = async (article_id) => {
    let sql = `delete from article where article_id = ${article_id} `
    return await executeSql(sql)
}

exports.updateArticle = async (data) => {
    console.log(data)
    let {
        article_id,
        title,
        content,
        publish_date,
        intro,
        status,
        cover,
        tag_ids,
        category_id,
        update_date,
        admin_id
    } = data

    let sql = `
    UPDATE article 
    SET 
        title = '${title}',
        content = '${content}',
        publish_date = ${publish_date},
        intro = '${intro}',
        status = ${status},
        cover = '${cover}',
        tag_ids = '${tag_ids}',
        category_id = ${category_id},
        update_date = ${update_date},
        admin_id = ${admin_id}
    WHERE article_id = ${article_id}
`
    return await executeSql(sql)
}